﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DigDes.DSchool.SUPS.DataAccess.Interface;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace DigDes.DSchool.SUPS.DataAccess.Database
{
    public class SelectorCargoDAC
    {
        private static readonly string ConnectionStringKey = "SUPSDB";

        public int Vurrent { get; set; }

        public static IEnumerable<CargoToDisplayDTO> SelectCargo(int MaximumRows, int StartRowIndex)
        {
            int pageIndex = StartRowIndex / MaximumRows + 1;
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {
                var sqlCmd = new SqlCommand("GetCargo", sqlConn);

                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.AddWithValue("Index", pageIndex);
                sqlCmd.Parameters.AddWithValue("PageSize", MaximumRows);
                                
                sqlConn.Open();
                SqlDataReader reader = sqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    yield return GetCargoFromReader(reader);
                }
                sqlConn.Close();
            }
        }

        public static IEnumerable<CargoToDisplayDTO> SelectCargoRow(int index, int pageIndex, int pageSize)
        {
            
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {
                var sqlCmd = new SqlCommand("GetCargoRow", sqlConn);
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.AddWithValue("Cargo_ID", index);
                sqlCmd.Parameters.AddWithValue("PageIndex", pageIndex);
                sqlCmd.Parameters.AddWithValue("PageSize", pageSize);
                sqlConn.Open();
                SqlDataReader reader = sqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    yield return GetCargoFromReader(reader);
                }
            }
        }

        private static CargoToDisplayDTO GetCargoFromReader(SqlDataReader reader)
        {
            CargoToDisplayDTO row = new CargoToDisplayDTO();
            if (!reader.IsDBNull(0))
            {
                row.Id = reader.GetInt32(0);
            }
            if (!reader.IsDBNull(1))
            {
                row.Name = reader.GetString(1);
            }
            if (!reader.IsDBNull(2))
            {
                row.Description = reader.GetString(2);
            }
            if (!reader.IsDBNull(3))
            {
                row.ShortCode = reader.GetString(3);
            }
            if (!reader.IsDBNull(4))
            {
                row.Code = reader.GetString(4);
            }
            if (!reader.IsDBNull(5))
            {
                row.NameETSNG = reader.GetString(5);
            }
            if (!reader.IsDBNull(6))
            {
                row.CodeGNG = reader.GetString(6);
            }

            if (!reader.IsDBNull(7))
            {
                row.NameGNG = reader.GetString(7);
            }

            if (!reader.IsDBNull(8))
            {
                row.Mnemocode = reader.GetString(8);
            }

            if (!reader.IsDBNull(9))
            {
                row.IsUsed = reader.GetBoolean(9);
            }

            if (!reader.IsDBNull(10))
            {
                row.IsEmpty = reader.GetBoolean(10);
            }
            return row;

        }

        public static void InsertCargo(	string cargoName, 
            string cargoDescription, 
            string cargoShortCode,
            string cargoCode,
            string cargoETSNGName, 
            string cargoCodeGNG, 
            string cargoGNGName, 
            string cargoMnemocode, 
            bool cargoIsUsed,
            bool cargoIsEmpty)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {

                var sqlCmd = new SqlCommand("SetCargoRow", sqlConn);
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.AddWithValue("CargoName", (cargoName == null) ? "" : cargoName);
                sqlCmd.Parameters.AddWithValue("CargoDescription", (cargoDescription == null) ? "" : cargoDescription);
                sqlCmd.Parameters.AddWithValue("CargoShortCode", (cargoShortCode == null) ? "" : cargoShortCode);
                sqlCmd.Parameters.AddWithValue("CargoCode", (cargoCode == null) ? "" : cargoCode);
                sqlCmd.Parameters.AddWithValue("CargoETSNGName", (cargoETSNGName == null) ? "" : cargoETSNGName);
                sqlCmd.Parameters.AddWithValue("CargoCodeGNG", (cargoCodeGNG == null) ? "" : cargoCodeGNG);
                sqlCmd.Parameters.AddWithValue("CargoGNGName", (cargoGNGName == null) ? "" : cargoGNGName);
                sqlCmd.Parameters.AddWithValue("CargoMnemocode", (cargoMnemocode == null) ? "" : cargoMnemocode);
                sqlCmd.Parameters.AddWithValue("CargoIsUsed", (cargoIsUsed == null) ? false : cargoIsUsed);
                sqlCmd.Parameters.AddWithValue("CargoIsEmpty", (cargoIsEmpty == null) ? false : cargoIsEmpty);
                
                sqlConn.Open();
                sqlCmd.ExecuteNonQuery();
            }
        }

        public static void UpdateCargo(int cargoID, string cargoName, string cargoDescription, string cargoShortCode, string cargoCode, string cargoETSNGName, string cargoCodeGNG, string cargoGNGName, string cargoMnemocode, bool cargoIsUsed, bool cargoIsEmpty)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
                        
            using (var sqlConn = new SqlConnection(connectionString))
            {

                var sqlCmd = new SqlCommand("EditCargoRow", sqlConn);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.AddWithValue("CargoID", (cargoID==null)? 0 :cargoID);
                sqlCmd.Parameters.AddWithValue("CargoName", (cargoName==null)? "" : cargoName);
                sqlCmd.Parameters.AddWithValue("CargoDescription", (cargoDescription==null)? "" : cargoDescription);
                sqlCmd.Parameters.AddWithValue("CargoShortCode", (cargoShortCode==null)? "" : cargoShortCode);
                sqlCmd.Parameters.AddWithValue("CargoCode", (cargoName==null)? "" : cargoName);
                sqlCmd.Parameters.AddWithValue("CargoETSNGName", (cargoETSNGName==null)? "" : cargoETSNGName);
                sqlCmd.Parameters.AddWithValue("CargoCodeGNG", (cargoCodeGNG==null)? "" : cargoCodeGNG);
                sqlCmd.Parameters.AddWithValue("CargoGNGName", (cargoGNGName==null)? "" : cargoGNGName);
                sqlCmd.Parameters.AddWithValue("CargoMnemocode", (cargoMnemocode==null)? "" : cargoMnemocode);
                sqlCmd.Parameters.AddWithValue("CargoIsUsed", (cargoIsUsed==null)? false : cargoIsUsed);
                sqlCmd.Parameters.AddWithValue("CargoIsEmpty", (cargoIsEmpty==null)? false : cargoIsEmpty);

                sqlConn.Open();
                sqlCmd.ExecuteNonQuery();
            }

        }

        public static void DeleteCargo(int cargoID)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {
                var sqlCmd = new SqlCommand("DeleteCargoRow", sqlConn);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.AddWithValue("Cargo_ID", cargoID);

                sqlConn.Open();
                sqlCmd.ExecuteNonQuery();
            }
        }

        public static int CountCargo()
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {
                var sqlCmd = new SqlCommand("GetCountOfCargo", sqlConn);
                sqlCmd.CommandType = CommandType.StoredProcedure;
                                
                sqlConn.Open();

                SqlDataReader reader = sqlCmd.ExecuteReader();

                //sqlCmd.ExecuteScalar();
                if (reader.Read())
                {
                    return (int)reader[0];
                }
                else
                {
                    return 0;
                }
                
            }
        }
    }
}
